/*

Input: migration_precincts.xlsx [data prepared by statistical office MUC]

Output: 	$tmp/migration_precincts.dta 

Tasks:
 * Prepare moves within and across precincts (Stimmbezirke)
 
Note:
	* Precinct boundaries are already as of 2018! no conversion needed
		


*/

import excel using "$rawdata/statistical_office/migration_precincts.xlsx",  clear
	
	// precinct ID
	rename A sb_new

	rename  (B C D)	(withmig1 outmig1 inmig1)	// LTW 13 (SE-13)
												// BTW 13 = LTW13 (FE-13 = SE-13)
	rename  (E F G)	(withmig3 outmig3 inmig3)	// KOW 14 (ME14)
												// EUW 14 = KOW14 (EuE14 = ME14)
	rename  (H I J)	(withmig5 outmig5 inmig5)	// BTW 17 (FE-17)
	rename  (K L M)	(withmig6 outmig6 inmig6)	// LTW 18 (SE-18)
	rename  (N O P)	(withmig7 outmig7 inmig7)	// EUW 19 (EuE19)
	rename  (Q R S)	(withmig8 outmig8 inmig8)	// KOW 20 (ME20)


	drop if _n < 5
	
	* Remove unnecessary "0" in the middle of the precinct ID 
	replace 	sb_new = substr(sb_new,1,2) +substr(sb_new,4,.)
	
	* Destring
	destring sb_new, replace
	drop if missing(sb_new)
	
	destring *, replace
	
	* Reshape long
	reshape long withmig outmig inmig, i(sb_new) j(wahl_id)
	
	
	* Impute FE-13 and EuE-14
	expand 2 if wahl_id==1, gen(tmp1)
	replace wahl_id=2 if tmp1==1
	
	expand 2 if wahl_id==3, gen(tmp2)
	replace wahl_id=4 if tmp2==1
	drop tmp*
	
	* Lab vars
	lab var sb_new 	"Harmonized precinct ID (as of 2018)"
	lab var withmig "Abs. migr. within precinct since last elec"
	lab var outmig 	"Abs. migr. out of precinct since last elec"
	lab var inmig 	"Abs. migr. into precinct since last elec"

	lab def wahl_id 0 "BTW2009" 1 "LTW2013" 2 "BTW2013" 3 "KOW2014" 4 "EUW2014" 5 "BTW2013" 6 "LTW2018" 7 "EUW2019" 8 "KOW2020"
	lab val wahl_id wahl_id
	lab var wahl_id "Election ID"	
	
	
	* Save: Panel of precincts (boundaries as of 2018) x election
	isid sb_new wahl_id
	save "$tmp/migration_precincts.dta", replace